﻿using System;
using System.Collections;
using System.Text;
using System.Data;
using GenaralLibs;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace Lists
{
    public class DMGiaTriTieuThuc : DoiTuong
    {
        #region TieuThucID property
        public int TieuThucID
        {
            get
            {
                return (int)_DataRow["TieuThucID"];
            }
            set
            {
                _DataRow["TieuThucID"] = value;
            }
        }
        #endregion
        #region NganhNghe_TieuThucID property
        public int NganhNghe_TieuThucID
        {
            get
            {
                return (int)_DataRow["NganhNghe_TieuThucID"];
            }
            set
            {
                _DataRow["NganhNghe_TieuThucID"] = value;
            }
        }
        #endregion
        #region HoSoID property
        public int HoSoID
        {
            get
            {
                return (int)_DataRow["HoSoID"];
            }
            set
            {
                _DataRow["HoSoID"] = value;
            }
        }
        #endregion

        #region GiaTri property
        public string GiaTri
        {
            get
            {
                return (string)_DataRow["GiaTri"];
            }
            set
            {
                _DataRow["GiaTri"] = value;
            }
        }
        #endregion

        public DMGiaTriTieuThuc(DataRow dataRow)
            : base(dataRow)
        {
        }
    }

    public class DanhMucGiaTriTieuThuc : DanhMucDoiTuong, IEnumerable
    {

        #region Instance property: Singleton Design pattern
        private static DanhMucGiaTriTieuThuc _Instance;
        public static DanhMucGiaTriTieuThuc Instance
        {
            get
            {
                if (_Instance == null)
                {
                    _Instance = new DanhMucGiaTriTieuThuc();
                    _Instance.DataView = _Instance.DataTable.DefaultView;
                }
                return _Instance;
            }
        }
        #endregion

        #region override TableName property
        public override string TableName
        {
            get
            {
                return "GiaTriTieuThuc";
            }
        }
        #endregion

        #region Methods required by IEnumerable (use for foreach)
        public IEnumerator GetEnumerator()
        {
            return new TokenEnumerator(this);
        }

        private class TokenEnumerator : IEnumerator
        {
            private int _Position = -1;
            DanhMucDoiTuong _Dstk;

            public TokenEnumerator(DanhMucDoiTuong dstk)
            {
                _Dstk = dstk;
            }

            // Declare the MoveNext method required by IEnumerator:
            public bool MoveNext()
            {
                if (_Position < _Dstk.DataView.Count - 1)
                {
                    _Position++;
                    return true;
                }
                else
                {
                    return false;
                }
            }

            // Declare the Reset method required by IEnumerator:
            public void Reset()
            {
                _Position = -1;
            }

            // Declare the Current property required by IEnumerator:
            public object Current
            {
                get
                {
                    return new DMGiaTriTieuThuc((_Dstk.DataView[_Position].Row));
                }
            }
        }
        #endregion

        public DMGiaTriTieuThuc this[int index]
        {
            get
            {
                if (0 <= index && index <= this.Count)
                {
                    return new DMGiaTriTieuThuc(DataTable.Rows[index]);
                }
                else
                {
                    return null;
                }
            }
        }

        public DMGiaTriTieuThuc NewDoiTuong()
        {
            try
            {
                return new DMGiaTriTieuThuc(Instance.DataTable.NewRow());
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public DMGiaTriTieuThuc GetByProperty(string property, object value)
        {
            try
            {
                DataRow[] rows = GetDataRows(property, value);
                if (rows.Length > 0) return new DMGiaTriTieuThuc(rows[0]);
                else return null;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static DanhMucGiaTriTieuThuc Filter(string filterExpression)
        {
            try
            {
                DanhMucGiaTriTieuThuc obj = new DanhMucGiaTriTieuThuc();
                obj.DataView = new DataView(DanhMucGiaTriTieuThuc.Instance.DataTable, filterExpression, "ID", DataViewRowState.CurrentRows);
                return obj;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static DanhMucGiaTriTieuThuc GetByHoSoID(int hoSoID)
        {
            return Filter(string.Format("HoSoID= '{0}'", hoSoID));
        }
        public static DanhMucGiaTriTieuThuc GetByHoSoIDTieuThuc(int hoSoID, int tieuThucID)
        {
            return Filter(string.Format("HoSoID= '{0}' AND TieuThucID= '{1}'", hoSoID, tieuThucID));
        }
        public DMGiaTriTieuThuc GetByID(int objID)
        {
            try
            {
                return GetByProperty("ID", objID);
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static DanhMucGiaTriTieuThuc GetAll()
        {
            try
            {
                return Instance;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        public DataTable LoadByHoSoIDTieuThuc(int hoSoID, int tieuThucID)
        {
            SqlConnection con = new SqlConnection(GenaralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                DataSet ds = new DataSet();
                ds.Clear();
                cmd.CommandText = string.Format("Select * from GiaTriTieuThuc where HoSoID= '{0}' and TieuThucID='{1}' ", hoSoID, tieuThucID);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(GenaralLibs.CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }
        public DataTable LoadByNganhNgheID(int nganhNgheID, int hoSoID)
        {
            SqlConnection con = new SqlConnection(GenaralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                DataSet ds = new DataSet();
                ds.Clear();
                cmd.CommandText = string.Format("Select * from TieuThucQuanLy where ID in(select TieuThucID from NganhNghe_TieuThuc where NganhNgheID = '{0}')",
                                                    nganhNgheID);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                DataTable table = new DataTable();
                table.Columns.Clear();
                //Tao header
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int k = 0; k < ds.Tables[0].Rows.Count; k++)
                    {
                        DataColumn column = new DataColumn();
                        column.Caption = ds.Tables[0].Rows[k][1].ToString();
                        column.ColumnName = ds.Tables[0].Rows[k][1].ToString();
                        table.Columns.Add(column);
                    }

                }
                //Tao row
                List<object> list = new List<object>();
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    DMTieuThucQuanLy dm = DanhMucTieuThucQuanLy.Instance.GetByProperty("TenTieuThuc", table.Columns[j].ColumnName);
                    DataTable dt = LoadByHoSoIDTieuThuc(hoSoID, dm.ID);
                    int kt = 0;
                    if (dt.Rows.Count > 0)
                    {
                        kt = Convert.ToInt32(dt.Rows[0]["GiaTri"].ToString());
                    }

                    list.Add(kt);
                }
                table.Rows.Add();
                for (int h = 0; h < list.Count; h++)
                {
                    table.Rows[0][h] = list[h];
                }

                return table;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(GenaralLibs.CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }
        public void DeleteByHoSoID(int hoSoID)
        {
            SqlConnection con = new SqlConnection(GenaralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("Delete from GiaTriTieuThuc where HoSoID= '{0}'", hoSoID);
                cmd.ExecuteNonQuery();
               
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(GenaralLibs.CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }
    }
}